﻿Imports SIGI.Common.BE
Imports SIGI.Common
Imports System.Data.SqlClient
Imports System.Configuration
Imports System.IO

Public Class OwnerDAC

#Region "<< -Search- >>"
    Public Shared Function Search(pActive As Boolean) As List(Of OwnerBE)
        Dim wOwnerBE As OwnerBE = New OwnerBE
        wOwnerBE.NotActive = Not pActive
        Return SearchByParams(wOwnerBE)
    End Function
#End Region

#Region "<< -SearchByParams- >>"
    Public Shared Function SearchByParams(pOwner As OwnerBE) As List(Of OwnerBE)
        Dim wSqlConn As SqlConnection = Nothing
        Dim wSqlCmd As SqlCommand = Nothing
        Dim wReader As SqlDataReader = Nothing
        Dim wConnStr As String = String.Empty

        Dim wOwnerBEList As List(Of OwnerBE) = Nothing
        Dim wOwnerBE As OwnerBE = Nothing

        Try
            'Obtengo la cadena de conexión
            wConnStr = ConfigurationManager.ConnectionStrings("connString").ConnectionString
            'Creo la conexión a la BD
            wSqlConn = New SqlConnection(wConnStr)
            'Creo el comando
            wSqlCmd = New SqlCommand()
            wSqlCmd.Connection = wSqlConn
            wSqlCmd.CommandType = CommandType.StoredProcedure
            wSqlCmd.CommandText = "Propietarios_g"

            wSqlCmd.Parameters.Add("@Id_Propietario", SqlDbType.Int)
            wSqlCmd.Parameters.Add("@Nombre", SqlDbType.NVarChar, 100)
            wSqlCmd.Parameters.Add("@Sexo", SqlDbType.Int)
            wSqlCmd.Parameters.Add("@Tipo_Doc", SqlDbType.Int)
            wSqlCmd.Parameters.Add("@DNI", SqlDbType.Decimal)
            wSqlCmd.Parameters.Add("@CUIT", SqlDbType.NVarChar, 15)
            wSqlCmd.Parameters.Add("@Codigo_Postal", SqlDbType.NVarChar, 10)
            wSqlCmd.Parameters.Add("@Direccion", SqlDbType.NVarChar, 50)
            wSqlCmd.Parameters.Add("@Id_Ciudad", SqlDbType.Int)
            wSqlCmd.Parameters.Add("@Id_Provincia", SqlDbType.Int)
            wSqlCmd.Parameters.Add("@Id_Pais", SqlDbType.Int)
            wSqlCmd.Parameters.Add("@Telefono1", SqlDbType.NChar, 30)
            wSqlCmd.Parameters.Add("@Telefono2", SqlDbType.NChar, 30)
            wSqlCmd.Parameters.Add("@Fecha_Baja", SqlDbType.DateTime)
            wSqlCmd.Parameters.Add("@Motivo_Baja", SqlDbType.NVarChar, 50)
            wSqlCmd.Parameters.Add("@Borrado", SqlDbType.Bit)

            wSqlCmd.Parameters("@Id_Propietario").Value = pOwner.Id
            If Not String.IsNullOrEmpty(pOwner.Name) Then
                wSqlCmd.Parameters("@Nombre").Value = pOwner.Name
            End If

            If Not pOwner.Sex Is Nothing Then
                wSqlCmd.Parameters("@Sexo").Value = pOwner.Sex.Id
            End If
            If Not pOwner.DocumentType Is Nothing Then
                wSqlCmd.Parameters("@Tipo_Doc").Value = pOwner.DocumentType.Id
            End If
            wSqlCmd.Parameters("@DNI").Value = pOwner.DocumentNumber
            If Not String.IsNullOrEmpty(pOwner.Cuit) Then
                wSqlCmd.Parameters("@CUIT").Value = pOwner.Cuit
            End If
            If Not String.IsNullOrEmpty(pOwner.PostalCode) Then
                wSqlCmd.Parameters("@Codigo_Postal").Value = pOwner.PostalCode
            End If
            If Not String.IsNullOrEmpty(pOwner.Direction) Then
                wSqlCmd.Parameters("@Direccion").Value = pOwner.Direction
            End If
            If Not pOwner.City Is Nothing Then
                wSqlCmd.Parameters("@Id_Ciudad").Value = pOwner.City.Id
            End If
            If Not pOwner.Province Is Nothing Then
                wSqlCmd.Parameters("@Id_Provincia").Value = pOwner.Province.Id
            End If
            If Not pOwner.Country Is Nothing Then
                wSqlCmd.Parameters("@Id_Pais").Value = pOwner.Country
            End If
            If Not String.IsNullOrEmpty(pOwner.Phone) Then
                wSqlCmd.Parameters("@Telefono1").Value = pOwner.Phone
            End If
            If Not String.IsNullOrEmpty(pOwner.AuxiliarPhone) Then
                wSqlCmd.Parameters("@Telefono2").Value = pOwner.AuxiliarPhone
            End If
            wSqlCmd.Parameters("@Fecha_Baja").Value = pOwner.EndDate
            If Not String.IsNullOrEmpty(pOwner.AccountCloseMotive) Then
                wSqlCmd.Parameters("@Motivo_Baja").Value = pOwner.AccountCloseMotive
            End If
            wSqlCmd.Parameters("@Borrado").Value = pOwner.NotActive

            wSqlConn.Open()

            wReader = wSqlCmd.ExecuteReader()
            wOwnerBEList = New List(Of OwnerBE)
            While wReader.Read
                wOwnerBE = New OwnerBE
                wOwnerBE.Id = Convert.ToInt32(wReader("Id_Propietario"))
                wOwnerBE.Name = Convert.ToString(wReader("Nombre"))
                'Sexo
                wOwnerBE.Sex = New SexBE
                wOwnerBE.Sex.Id = Convert.ToInt32(wReader("IdSexo"))
                wOwnerBE.Sex.Description = Convert.ToString(wReader("Sexo"))
                'Tipo Documento
                wOwnerBE.DocumentType = New DocumentTypeBE
                wOwnerBE.DocumentType.Id = Convert.ToInt32(wReader("IdTipoDocumento"))
                wOwnerBE.DocumentType.Description = Convert.ToString(wReader("TipoDocumento"))
                wOwnerBE.DocumentNumber = Convert.ToDecimal(wReader("DNI"))
                wOwnerBE.Cuit = Convert.ToString(wReader("CUIT"))
                wOwnerBE.PostalCode = Convert.ToString(wReader("Codigo_Postal"))
                wOwnerBE.Direction = Convert.ToString(wReader("Direccion"))
                'Ciudad
                wOwnerBE.City = New CityBE
                wOwnerBE.City.Id = Convert.ToInt32(wReader("Id_Ciudad"))
                wOwnerBE.City.Description = Convert.ToString(wReader("Ciudad"))
                'Provincia
                wOwnerBE.Province = New ProvinceBE
                wOwnerBE.Province.Id = Convert.ToInt32(wReader("Id_Provincia"))
                wOwnerBE.Province.Description = Convert.ToString(wReader("Provincia"))
                'País
                wOwnerBE.Country = New CountryBE
                wOwnerBE.Country.Id = Convert.ToInt32(wReader("Id_Pais"))
                wOwnerBE.Country.Description = Convert.ToString(wReader("Pais"))
                wOwnerBE.Phone = Convert.ToString(wReader("Telefono1"))
                wOwnerBE.AuxiliarPhone = Convert.ToString(wReader("Telefono2"))
                If Not wReader("Fecha_Baja") Is DBNull.Value Then
                    wOwnerBE.EndDate = Convert.ToDateTime(wReader("Fecha_Baja"))
                End If
                wOwnerBE.AccountCloseMotive = Convert.ToString(wReader("Motivo_Baja"))
                wOwnerBE.NotActive = Convert.ToBoolean(wReader("Borrado"))

                wOwnerBEList.Add(wOwnerBE)
            End While

            wSqlConn.Close()
        Catch ex As Exception
            Throw ex
        End Try

        Return wOwnerBEList
    End Function
#End Region

#Region "<< -Insert- >>"
    Public Shared Sub Insert(pOwner As OwnerBE)
        Dim wSqlConn As SqlConnection = Nothing
        Dim wSqlCmd As SqlCommand = Nothing
        Dim wReader As SqlDataReader = Nothing
        Dim wConnStr As String = String.Empty
        Dim wSqlTran As SqlTransaction = Nothing
        Dim wQuery As System.Text.StringBuilder = Nothing
        Dim wQueryImg As System.Text.StringBuilder = Nothing
        Dim wQuerySvc As System.Text.StringBuilder = Nothing
        Dim wNewOwnerId As Integer = 0
        Dim wDirectory As String = String.Empty
        Dim wImgName As String = String.Empty
        Dim wFInfo As FileInfo = Nothing

        Try


            'Obtengo la cadena de conexión
            wConnStr = ConfigurationManager.ConnectionStrings("connString").ConnectionString
            'Creo la conexión a la BD
            wSqlConn = New SqlConnection(wConnStr)

            wSqlConn.Open()
            'Creo la transacción
            wSqlTran = wSqlConn.BeginTransaction("Transaction")
            'Creo el comando
            wSqlCmd = New SqlCommand()
            wSqlCmd.Transaction = wSqlTran

            wSqlCmd.Connection = wSqlConn
            wSqlCmd.CommandType = CommandType.StoredProcedure
            wSqlCmd.CommandText = "Propietarios_i"

            wSqlCmd.Parameters.Add("@Nombre", SqlDbType.NVarChar, 100).Value = pOwner.Name
            wSqlCmd.Parameters.Add("@Sexo", SqlDbType.Int).Value = pOwner.Sex.Id
            wSqlCmd.Parameters.Add("@Tipo_Doc", SqlDbType.Int).Value = pOwner.DocumentType.Id
            wSqlCmd.Parameters.Add("@DNI", SqlDbType.Decimal).Value = pOwner.DocumentNumber
            wSqlCmd.Parameters.Add("@CUIT", SqlDbType.NVarChar, 15).Value = pOwner.Cuit
            wSqlCmd.Parameters.Add("@Codigo_Postal", SqlDbType.NVarChar, 10).Value = pOwner.PostalCode
            wSqlCmd.Parameters.Add("@Direccion", SqlDbType.NVarChar, 50).Value = pOwner.Direction
            wSqlCmd.Parameters.Add("@Id_Ciudad", SqlDbType.Int).Value = pOwner.City.Id
            wSqlCmd.Parameters.Add("@Id_Provincia", SqlDbType.Int).Value = pOwner.Province.Id
            wSqlCmd.Parameters.Add("@Id_Pais", SqlDbType.Int).Value = pOwner.Country.Id
            wSqlCmd.Parameters.Add("@Telefono1", SqlDbType.NChar, 30)
            wSqlCmd.Parameters.Add("@Telefono2", SqlDbType.NChar, 30)
            wSqlCmd.Parameters.Add("@Fecha_Baja", SqlDbType.DateTime).Value = pOwner.EndDate
            wSqlCmd.Parameters.Add("@Motivo_Baja", SqlDbType.NVarChar, 50)
            wSqlCmd.Parameters.Add("@Borrado", SqlDbType.Bit).Value = False

            If Not String.IsNullOrEmpty(pOwner.Phone) Then
                wSqlCmd.Parameters("@Telefono1").Value = pOwner.Phone
            End If
            If Not String.IsNullOrEmpty(pOwner.AuxiliarPhone) Then
                wSqlCmd.Parameters("@Telefono2").Value = pOwner.AuxiliarPhone
            End If
            If Not String.IsNullOrEmpty(pOwner.AccountCloseMotive) Then
                wSqlCmd.Parameters("@Motivo_Baja").Value = pOwner.AccountCloseMotive
            End If

            wReader = wSqlCmd.ExecuteReader()
            wReader.Read()
            wNewOwnerId = Convert.ToInt32(wReader(0))
            wReader.Close()

            'Inserto las propiedades
            wSqlCmd.CommandType = CommandType.Text
            wSqlCmd.Parameters.Clear()

            wQuery = New System.Text.StringBuilder
            wQueryImg = New System.Text.StringBuilder
            wQuerySvc = New System.Text.StringBuilder
            For Each wProperty As PropertyBE In pOwner.Properties
                wQuery.Clear()

                'Servicios de la propiedad
                If Not wProperty.Services Is Nothing Then
                    wQuerySvc.Clear()
                    For Each wSrv As ServicesBE In wProperty.Services
                        wQuerySvc.Append("EXEC ServiciosXPropiedades_i ")
                        wQuerySvc.Append(" @Id_Propiedad = [Id_Propiedad], ")
                        wQuerySvc.Append(String.Format(" @Id_Servicio = {0} ", wSrv.Id))
                        wQuerySvc.AppendLine()
                    Next
                End If

                wQuery.Append("EXEC Propiedades_i ")
                wQuery.Append(String.Format(" @Nombre = '{0}', ", wProperty.Name))
                If String.IsNullOrEmpty(wProperty.Description) Then
                    wQuery.Append(" @Descripcion = NULL, ")
                Else
                    wQuery.Append(String.Format(" @Descripcion = '{0}', ", wProperty.Description))
                End If
                wQuery.Append(String.Format(" @Id_Estado = {0}, ", wProperty.Status.Id))
                wQuery.Append(String.Format(" @Tipo_Inmueble = {0}, ", wProperty.Type.Id))
                wQuery.Append(String.Format(" @Ubicacion = '{0}', ", wProperty.Direction))
                wQuery.Append(String.Format(" @Id_Ciudad = {0}, ", wProperty.City.Id))
                If (String.IsNullOrEmpty(wProperty.Observations)) Then
                    wQuery.Append(" @Observaciones = NULL, ")
                Else
                    wQuery.Append(String.Format(" @Observaciones = '{0}', ", wProperty.Observations))
                End If
                wQuery.Append(String.Format(" @Id_Propietario = {0}, ", wNewOwnerId))
                wQuery.Append(String.Format(" @Id_Provincia = {0}, ", wProperty.Province.Id))
                wQuery.Append(String.Format(" @Id_Pais = {0}, ", wProperty.Country.Id))
                wQuery.Append(" @Id_Contrato = NULL, ")
                If wQuerySvc.Length > 0 Then
                    wQuery.Append(String.Format("@Servicios = '{0}' ", wQuerySvc.ToString()))
                Else
                    wQuery.Append("@Servicios = NULL ")
                End If

                wQuery.AppendLine()

                wSqlCmd.CommandText = wQuery.ToString()
                wReader = wSqlCmd.ExecuteReader()
                wReader.Read()
                wProperty.Id = Convert.ToInt32(wReader(0))
                wReader.Close()

                'Ruta el directorio de imagenes para esta propiedad
                wDirectory = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, Common.Constants.PropertiesImagesDirectory, wProperty.Id.ToString())
                'Creo el directorio de imágenes
                Directory.CreateDirectory(wDirectory)
                'Ubicación de la portada
                If Not String.IsNullOrEmpty(wProperty.MainImage) Then
                    wFInfo = New FileInfo(wProperty.MainImage)
                    wImgName = Path.Combine(wDirectory, wFInfo.Name)
                    'Guardo la imagen de la portada
                    File.Copy(wProperty.MainImage, wImgName, True)

                    wSqlCmd.CommandType = CommandType.Text

                    wSqlCmd.CommandText = String.Format("UPDATE dbo.Propiedades SET ImagenPortada = '{0}' WHERE Id_Propiedad = {1}",
                                                        wImgName, wProperty.Id)
                End If

                wSqlCmd.ExecuteNonQuery()

                'Imagenes de la propiedad
                If wProperty.SecundaryImages Is Nothing Then Continue For
                For Each wImg As ImagesPerPropertyBE In wProperty.SecundaryImages
                    'Obtengo el nombre de la imágen
                    wImgName = wImg.Description.Substring(wImg.Description.LastIndexOf("\") + 1)
                    'Creo la imagen
                    wImgName = Path.Combine(wDirectory, wImgName)
                    File.Copy(wImg.Description, wImgName, True)

                    wQueryImg.Append("EXEC ImagenesXPropiedades_i ")
                    wQueryImg.Append(String.Format(" @Id_Propiedad = {0}, ", wProperty.Id))
                    wQueryImg.Append(String.Format(" @Imagen = '{0}' ", wImgName))
                    wQuery.AppendLine()
                Next
            Next

            'Inserto la imagenes de todas las propiedades en la base
            If wQueryImg.Length > 0 Then
                wSqlCmd.CommandText = wQueryImg.ToString()
                wSqlCmd.ExecuteNonQuery()
            End If

            wSqlTran.Commit()

            wSqlConn.Close()
        Catch ex As Exception
            'Por si falla el rollback de la transacción
            Try
                If Not String.IsNullOrEmpty(wDirectory) AndAlso Directory.Exists(wDirectory) Then
                    Directory.Delete(wDirectory, True)
                End If
                wSqlTran.Rollback()
                Throw ex
            Catch ex2 As Exception
                Throw ex2
            End Try

            Throw ex
        End Try
    End Sub
#End Region

#Region "<< -Update- >>"
    Public Shared Sub Update(pOwner As OwnerBE)
        Dim wSqlConn As SqlConnection = Nothing
        Dim wSqlCmd As SqlCommand = Nothing
        Dim wConnStr As String = String.Empty
        Dim wTmp As String() = Nothing

        Try
            'Obtengo la cadena de conexión
            wConnStr = ConfigurationManager.ConnectionStrings("connString").ConnectionString
            'Creo la conexión a la BD
            wSqlConn = New SqlConnection(wConnStr)
            'Creo el comando
            wSqlCmd = New SqlCommand()
            wSqlCmd.Connection = wSqlConn
            wSqlCmd.CommandType = CommandType.StoredProcedure
            wSqlCmd.CommandText = "Propietarios_u"

            wSqlCmd.Parameters.Add("@Id_Propietario", SqlDbType.Int)
            wSqlCmd.Parameters.Add("@Nombre", SqlDbType.NVarChar, 100)
            wSqlCmd.Parameters.Add("@Sexo", SqlDbType.Int)
            wSqlCmd.Parameters.Add("@Tipo_Doc", SqlDbType.Int)
            wSqlCmd.Parameters.Add("@DNI", SqlDbType.Decimal)
            wSqlCmd.Parameters.Add("@CUIT", SqlDbType.NVarChar, 15)
            wSqlCmd.Parameters.Add("@Codigo_Postal", SqlDbType.NVarChar, 10)
            wSqlCmd.Parameters.Add("@Direccion", SqlDbType.NVarChar, 50)
            wSqlCmd.Parameters.Add("@Id_Ciudad", SqlDbType.Int)
            wSqlCmd.Parameters.Add("@Id_Provincia", SqlDbType.Int)
            wSqlCmd.Parameters.Add("@Id_Pais", SqlDbType.Int)
            wSqlCmd.Parameters.Add("@Telefono1", SqlDbType.NChar, 30)
            wSqlCmd.Parameters.Add("@Telefono2", SqlDbType.NChar, 30)
            wSqlCmd.Parameters.Add("@Fecha_Baja", SqlDbType.DateTime)
            wSqlCmd.Parameters.Add("@Motivo_Baja", SqlDbType.NVarChar, 50)
            wSqlCmd.Parameters.Add("@Borrado", SqlDbType.Bit)

            wSqlCmd.Parameters("@Id_Propietario").Value = pOwner.Id
            wSqlCmd.Parameters("@Nombre").Value = pOwner.Name
            wSqlCmd.Parameters("@Sexo").Value = pOwner.Sex.Id
            wSqlCmd.Parameters("@Tipo_Doc").Value = pOwner.DocumentType.Id
            wSqlCmd.Parameters("@DNI").Value = pOwner.DocumentNumber
            wSqlCmd.Parameters("@CUIT").Value = pOwner.Cuit
            wSqlCmd.Parameters("@Codigo_Postal").Value = pOwner.PostalCode
            wSqlCmd.Parameters("@Direccion").Value = pOwner.Direction
            wSqlCmd.Parameters("@Id_Ciudad").Value = pOwner.City.Id
            wSqlCmd.Parameters("@Id_Provincia").Value = pOwner.Province.Id
            wSqlCmd.Parameters("@Id_Pais").Value = pOwner.Country.Id
            wSqlCmd.Parameters("@Telefono1").Value = pOwner.Phone
            wSqlCmd.Parameters("@Telefono2").Value = pOwner.AuxiliarPhone
            wSqlCmd.Parameters("@Fecha_Baja").Value = pOwner.EndDate
            wSqlCmd.Parameters("@Motivo_Baja").Value = pOwner.AccountCloseMotive
            wSqlCmd.Parameters("@Borrado").Value = pOwner.NotActive

            wSqlConn.Open()
            wSqlCmd.ExecuteNonQuery()
            wSqlConn.Close()
        Catch ex As Exception
            Throw ex
        End Try
    End Sub
#End Region

#Region "<< -Delete- >>"
    Public Shared Function Delete(pOwner As OwnerBE) As Boolean
        Dim wSqlConn As SqlConnection = Nothing
        Dim wSqlCmd As SqlCommand = Nothing
        Dim wConnStr As String = String.Empty
        Dim wReader As SqlDataReader = Nothing
        Dim wDeleted As Boolean = False

        Try
            'Obtengo la cadena de conexión
            wConnStr = ConfigurationManager.ConnectionStrings("connString").ConnectionString
            'Creo la conexión a la BD
            wSqlConn = New SqlConnection(wConnStr)
            'Creo el comando
            wSqlCmd = New SqlCommand()
            wSqlCmd.Connection = wSqlConn
            wSqlCmd.CommandType = CommandType.StoredProcedure
            wSqlCmd.CommandText = "Propietarios_d"

            wSqlCmd.Parameters.Add("@Id_Propietario", SqlDbType.Int)
            wSqlCmd.Parameters.Add("@Fecha_Baja", SqlDbType.DateTime)
            wSqlCmd.Parameters.Add("@Motivo_Baja", SqlDbType.NVarChar, 50)

            wSqlCmd.Parameters("@Id_Propietario").Value = pOwner.Id
            wSqlCmd.Parameters("@Fecha_Baja").Value = DateAndTime.Today
            wSqlCmd.Parameters("@Motivo_Baja").Value = pOwner.AccountCloseMotive

            wSqlConn.Open()

            wReader = wSqlCmd.ExecuteReader()
            wReader.Read()
            wDeleted = Convert.ToBoolean(wReader(0))

            wSqlConn.Close()
        Catch ex As Exception
            Throw ex
        End Try

        Return wDeleted
    End Function
#End Region

End Class
